package com.etown.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.log4j.Logger;

/**
 * 通用DAO
 * @author knight
 *
 */
public class CommonDAO {
	private static Logger log=Logger.getLogger(CommonDAO.class);
	
	private CommonDAO(){}
	
	/**
	 * 获取JSON数组
	 * @param sql
	 * @param params
	 * @return
	 */
	public static JSONArray getList(String sql, Object... params) {
		Connection con=DBPool.getConnection();
		final JSONArray jsonArr = new JSONArray();
		try {
			QueryRunner runner = new QueryRunner();
			runner.query(con,sql.toString(), new ResultSetHandler<Object>() {
				public Object handle(ResultSet rs) throws SQLException {
					ResultSetMetaData metaData = rs.getMetaData();
					int cols = metaData.getColumnCount();
					while (rs.next()) {
						JSONObject jsonObj = new JSONObject();
						for (int i = 1; i <= cols; i++) {
							String colName = metaData.getColumnLabel(i);
							String value = rs.getString(colName);
							if (value != null && value.length() != 0) {
								jsonObj.put(colName.toLowerCase(), value);// 列名全部转换成小写，便于前台代码统一处理
							}
						}
						jsonArr.add(jsonObj);
					}
					return jsonArr;
				}
			}, params);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(), e);
		}finally{
			DBPool.releaseConnection(con);
		}
		return jsonArr;
	}
	
	/**
	 * 获取单条JSON记录
	 * @param sql
	 * @param params
	 * @return
	 */
	public static JSONObject getSingle(String sql, Object... params) {
		JSONArray result = getList(sql, params);
		if (result == null || result.size() == 0) {
			return null;
		}
		return result.getJSONObject(0);
	}
	
	
	/**
	 * 更新表
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(String sql, Object... params) {
		Connection con=DBPool.getConnection();
		try {
			QueryRunner runner = new QueryRunner();
			return runner.update(con,sql, params);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}finally{
			DBPool.releaseConnection(con);
		}
		return 0;
	}
}
